/*

This module provides access to a jdbc connection over RMI

getTables()
getFields(String tableName)
createTable(String tableName, String[] rows)
insertRow(String tableName, Object[] row)
select(String tableName, String fieldName, Object fieldValue)
selectAll(String tableName)

It is expected that every time the database is written to,
a notification should be sent out.

*/

package javasec.seed.data;

import java.io.File;
import java.util.ArrayList;
import java.rmi.RemoteException;

import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javasec.seed.DataSeedInterface;
import javasec.seed.ManagerSeed;
import javasec.seed.Seed;
import javasec.seed.SeedInterface;
import javasec.seed.SeedNotification;
import javasec.seed.SeedServer;



public class SQLiteDataSeed extends Seed implements SeedInterface, DataSeedInterface
{
	private static final long serialVersionUID = 4397150604244071321L;
	public Connection conn=null; // the jdbc connector
	public SecureRandom rng=null;
	
	public SQLiteDataSeed() throws RemoteException
	{
		System.out.println("SeedData: new SeedData object instantiated");
		try
		{
			Class.forName("org.sqlite.JDBC");
			conn=DriverManager.getConnection("jdbc:sqlite:"+SeedServer.seedHome+File.separator+"seed.db");
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	
		rng=new SecureRandom();
		rng.setSeed(System.currentTimeMillis());
		
		createTable("TAGS",new String[]
		{
			"VALUE text not null",
			"ASSET_TABLE text not null",
			"ASSET_ID integer not null"
		});
		
		createTable("RELATIONS",new String[]
		{
			"VALUE text not null",
			"ASSET1_TABLE text not null",
			"ASSET1_ID integer not null",
			"ASSET2_TABLE text not null",
			"ASSET2_ID integer not null"	
		});
	}
	
	public String[] getTables()
	{
		ArrayList<String> tableNames = new ArrayList<String>();
		DatabaseMetaData metaData;
		ResultSet result;
			
		try
		{
			metaData=conn.getMetaData();
			result=metaData.getTables(null,null,null,new String[]{"TABLE"});
				
			while(result.next())
			{
				String tableName=result.getString(3);
				
				// check table names against a blacklist
				//if(tableName.equals("TAGS"))continue;
				//if(tableName.equals("RELATIONS"))continue;
				if(tableName.equals("SQLITE_SEQUENCE"))continue;
				
				tableNames.add(tableName);	
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
			
		System.out.println("returning "+tableNames.size()+" table names");
		return (String[]) tableNames.toArray(new String[]{});
	}

	public String[] getFields(String tableName)
	{
		DatabaseMetaData meta;
		ResultSet columns=null;
		ArrayList<String> columnNames=new ArrayList<String>();
		
		// query the database for the rows and columns
		try
		{
			meta=conn.getMetaData();
			columns=meta.getColumns(null, null, tableName, null);
			
			// compile a list that contains names for each column
			while(columns.next())columnNames.add(columns.getString(4));
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return (String[]) columnNames.toArray(new String[]{});
	}

	// new table
	public boolean createTable(String tableName,String[] rows)
	{
		// make sure rowNames.count is the same as rowClasses.count
		String command="CREATE TABLE "+tableName+" ( ID integer primary key, TIMESTAMP text, ";
		for(int x=0;x<rows.length;++x)
		{
			command=command+rows[x];
			if(x!=rows.length-1)command+=", ";
			else command+=")";
		}

		// run the statement
		try
		{
			Statement s=conn.createStatement();
			s.executeUpdate(command);
		}
		catch(Exception e)
		{
			System.out.println("SQLiteDataSeed: table '"+tableName+"' already exists");
			//e.printStackTrace();
		}
		
		System.out.println("looks like the table creation worked..");
		return true;
	}

	// insert row
	public boolean insertRow(String tableName, Object[] row)
	{
		try
		{
			// this might not work well because of index
			// we might need to getFields()
			String[] fields=getFields(tableName);
			if(fields.length-2!=row.length) // -1 to ignore the ID/TIMESTAMP field
			{
				System.out.println("can't insert row, the counts don't match ("+
					fields.length+", "+row.length+")");
				return false;
			}
			
			String command="INSERT INTO "+tableName+"(ID, TIMESTAMP,";
			for(int x=2;x<fields.length;++x) // start at 2 to ignore ID/TIMESTAMP field (auto incremented)
			{
				command+=fields[x];
				if((x+1)==fields.length)command+=")"; // finish off the statement...
					else command+=",";                // ...or prepare for a new object
			}

			int randID=rng.nextInt();
			if(randID<1)randID*=-1; // make sure randID is a positive int
			String randIDString=""+randID;
			String timeString=""+System.currentTimeMillis();
			
			command+=" values("+randID+","+timeString+",";
			for(int x=0;x<row.length;++x)
			{
				command+="?";
				if((x+1)==row.length)command+=")"; // finish off the statement...
					else command+=",";             // ...or prepare for a new object
			}

			System.out.println(command);
			
			PreparedStatement ps=conn.prepareStatement(command);

			for(int x=0;x<row.length;++x)
			{
				// sql starts counting at 1
				ps.setObject(x+1,row[x]);
			}

			ps.executeUpdate(); // this does not actually return row number
			
			// add the ID and timestamp into the array
			Object[] fullRow=new Object[row.length+2];
			fullRow[0]=randIDString;
			fullRow[1]=timeString;
			for(int x=0;x<row.length;++x)fullRow[x+2]=row[x];
			
			//notify everyone about the new row
			ManagerSeed.notifyClients(new SeedNotification("data.newRow."+tableName,fullRow));
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return true;
	}	

	//update row
	public boolean updateRow(String tableName, Integer id, ArrayList<Object> row)
	{
		System.out.println("update row not implemented!");
		return false;
	}

	// update field
	public boolean updateField(String tableName, Integer id,String fieldName, Object value)
	{
		if(fieldName.equals("ID"))return false;
		
		PreparedStatement ps=null;
		try
		{
			ps=conn.prepareStatement("UPDATE "+tableName+" SET "+fieldName+"=? where ID=?");
			ps.setObject(1, value);
			ps.setInt(2, id.intValue());
			
			int rows=ps.executeUpdate();
			
			ManagerSeed.notifyClients(new SeedNotification("data.updatedField."+tableName,new Object[]{id,fieldName,value}));
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		
		return true;
	}

	// update TODO  GENERALIZE THIS
	//public Object[][] select(String tableName, String fieldName, Object value)
	public Object[][] select(String tableName, Object[] query)
	{
		if(query.length%2!=0)return null; // make sure length is a multiple of 2
		
		ResultSet rows=null;
		Statement statement=null;
		ArrayList<Object[]> rowList=new ArrayList<Object[]>();
		int numFields=getFields(tableName).length;		
		
		try
		{
			statement = conn.createStatement();
			
			// TODO  there is a better more sqly way to do this
			rows=statement.executeQuery("select * from "+tableName);

			// label the loop for double breakout
			loop: while(rows.next())
			{
				// make sure this row matches the query
				for(int x=0;x<query.length;x+=2)
				{
					// double breakout continue attack!
					if(!rows.getObject(query[x].toString()).equals(query[x+1]))continue loop;
				}
				
				// this row matches, add it to the list
				Object[] currentRow=new Object[numFields];
				for(int x=0;x<numFields;++x)
				{
					currentRow[x]=rows.getObject(1+x); // sql starts counting at 1
				}
				rowList.add(currentRow);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		}
		
		return (Object[][]) rowList.toArray(new Object[][]{});
	}

	public Object[][] selectAll(String tableName)
	{
		ResultSet rows=null;
		Statement statement=null;
		ArrayList<Object[]> rowList=new ArrayList<Object[]>();
		int numFields=getFields(tableName).length;
		
		try
		{
			statement = conn.createStatement();
			rows=statement.executeQuery("select * from "+tableName);

			while(rows.next())
			{
				Object[] currentRow=new Object[numFields];
				for(int x=0;x<numFields;++x)
				{
					currentRow[x]=rows.getObject(x+1); // sql starts counting at 1
				}
				rowList.add(currentRow);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		return (Object[][]) rowList.toArray(new Object[][]{});
	}
	
	public boolean deleteTable(String tableName)
	{
		System.out.println("delete table not implemented!");
		return false;
	}
	
	public boolean deleteRow(String tableName, Integer rowID)
	{
		System.out.println("delete row not implemented!");
		return false;
	}
	
	public String tagAsset(String value, String aTable, Integer aID)
	{
		insertRow("TAGS",new Object[]{value,aTable,aID});
		return null;
	}
	
	public String relateAssets(String value, String aTable, Integer aID, String bTable, Integer bID)
	{
		insertRow("RELATIONS",new Object[]{value,aTable,aID,bTable,bID});
		return null;
	}

	public byte[] exportXML() throws RemoteException
	{
		// TODO Auto-generated method stub
		return null;
	}

	public int importXML(byte[] xmlFile) throws RemoteException
	{
		// TODO Auto-generated method stub
		return 0;
	}
}
